# Written by Ashoka D. Polpitiya
# for the Translational Genomics Research Institute (TGen, Phoenix, AZ)
# Copyright 2012, Translational Genomics Research Institute
# E-mail: ashoka@tgen.org
# Website: http://paRseMSF.googlecode.com
# -------------------------------------------------------------------------
#
# Licensed under the Apache License, Version 2.0; you may not use this file except
# in compliance with the License.  You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
#
# -------------------------------------------------------------------------

protModsTable <- function(msfFile, minConf="High", proteins=TRUE)
{
    require(RSQLite)
    require(sqldf)
    driver = dbDriver("SQLite")
    con <- dbConnect(driver, dbname=msfFile)

    confidence <- switch(minConf,
              High = 3,
              Medium = 2,
              Low = 1,
              3)

    ModsTable <- pepModsTable(msfFile, minConf=minConf)
    
    if (proteins)
    {
        # Proteins (ProteinID and Description)
        sql_prots <- paste("SELECT DISTINCT pr.ProteinID AS Proteins, REPLACE(prAn.Description,'>','') AS Description",
                        "FROM Peptides p, PeptidesProteins pr, ProteinAnnotations prAn",
                        "WHERE p.PeptideID = pr.PeptideID AND",
                        "Proteins = prAn.ProteinID AND",
                        "p.ConfidenceLevel >=", confidence, ";")
        rs_prots <- dbSendQuery(con, sql_prots)
        prots <- fetch(rs_prots, n=-1)
        dbClearResult(rs_prots);
        # Split Protein Description into Protein Name and Annotation fields
        X <- matrix(unlist(lapply(prots[,2],protIDsplitter)),ncol=2,byrow=TRUE)
        # Proteins (ProteinID, ProteinName, Annotation)
        prots <- data.frame(ProteinID=prots[,1], Name=X[,1], Annotation=X[,2])

        # Peptides and Proteins
        sql_pepprots <- paste("SELECT p.PeptideID, pr.ProteinID",
                          "FROM Peptides AS p LEFT OUTER JOIN PeptidesProteins AS pr",
                          "ON (p.PeptideID = pr.PeptideID)",
                          "WHERE p.ConfidenceLevel >=", confidence,
                          "ORDER BY p.PeptideID;")
        rs_pepprots <- dbSendQuery(con, sql_pepprots)
        pepsProts <- fetch(rs_pepprots, n=-1)
        dbClearResult(rs_pepprots);
        # Create a table with peptides and protein info
        pepsProts <- merge(pepsProts, prots, by="ProteinID", all.x=TRUE)

        # Aggregate Protein names for each peptide
        protModsTab <- sqldf("SELECT p.*, group_concat(DISTINCT pr.Name) AS Proteins FROM ModsTable p,
                  pepsProts pr WHERE p.PeptideID=pr.PeptideID GROUP BY p.PeptideID;")

        ModsTable <- protModsTab[order(as.numeric(protModsTab[,1])),]
        }
    #Cleanup
    dbDisconnect(con);
    return(ModsTable)
}

# Run
# M <- protModsTable(msfFile="test_msf.msf", minConf="High", proteins=TRUE)

